﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OracleClient;
using System.Data;
using System.Collections;
using System.Xml;
using Aspose.Words;
using Maticsoft.DBUtility;

namespace SkWeb.FASJ
{
    public partial class GSSJ : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

            }
        }


        protected void btnUplaod_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile)
            {
                //判断文件是否小于10Mb  
                if (FileUpload1.PostedFile.ContentLength < 10485760)
                {
                    try
                    {
                        FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Files/")
                            + FileUpload1.FileName);
                        Document tempDocument;
                        tempDocument = new Document(Server.MapPath("~/Files/") + FileUpload1.FileName);
                        hiddenPath.Value = tempDocument.GetText().Replace("\f","");
                        

                        lblMessage.Text = "上传成功!";

                        this.GridView1.DataSource = GetBookMarksFromDoc(Server.MapPath("~/Files/")
                            + FileUpload1.FileName);
                        this.GridView1.DataBind();
                        this.ddlTbName.DataSource = DtDropList;
                        ddlTbName.DataTextField = "TABLE_NAME";
                        ddlTbName.DataBind();
                        ddlTbName.Items.Insert(0, new ListItem(" "));
                        this.ddlTbName.SelectedIndexChanged += new EventHandler(ddlTbName_SelectedIndexChanged);
                        
                    }
                    catch (Exception ex)
                    {
                        lblMessage.Text = ex.Message;
                        
                    }
                }
                else
                {
                    lblMessage.Text = "上传文件不能大于10MB!";
                }
            }
            else
            {
                lblMessage.Text = "尚未选择文件!";
            }
        }


        List<MyBookMark1> GetBookMarksFromDoc(string fileName)
        {
            List<MyBookMark1> bookMarks = new List<MyBookMark1>();

            try
            {
                bookMarks = Tools1.GetBookMarksFromDoc(fileName);
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
            return bookMarks;
        }

        DataTable dtDropList = null;
        DataTable DtDropList
        {
            get
            {
                if (dtDropList == null)
                {
                    dtDropList = GetTableNames();
                }
                return dtDropList;
            }
            set
            {
                dtDropList = value;
            }
        }

        DataTable GetTableNames()
        {//select a.table_name as tablename from user_tab_cols a   where a.column_name='qzh'
            //string strSql = " SELECT table_name FROM USER_TABLES where table_name in "
            //    + " (select a.table_name as tablename from user_tab_cols a   where a.column_name='ID')";
            string strSql = "select  table_name||'  ('||comments||')' table_name from  user_tab_comments where table_type='TABLE' and table_name in "
                + " (select a.table_name as tablename from user_tab_cols a   where a.column_name='ID')";
            DataTable dt = OracleHelper.Query(strSql).Tables[0];
            foreach (DataRow dr in dt.Rows)
            {
                dr["table_name"] = dr["table_name"].ToString().Replace("()", "");
            }
            dt.AcceptChanges();
            return dt;
        }




        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DropDownList ddlTableName = e.Row.FindControl("ddlTableName") as DropDownList;
                ddlTableName.DataSource = DtDropList;
                ddlTableName.DataTextField = "TABLE_NAME";
                ddlTableName.DataBind();
                ddlTableName.Items.Insert(0, new ListItem(" "));

                //ddlTableName = e.Row.FindControl("ddlPkTableName") as DropDownList;
                //ddlTableName.DataSource = DtDropList;
                //ddlTableName.DataTextField = "TABLE_NAME";
                //ddlTableName.DataBind();
                //ddlTableName.Items.Insert(0, new ListItem(" "));
            }
        }


        protected void ddlTableName_SelectedIndexChanged(object sender, EventArgs e)
        {
            DropDownList ddlTableNames = sender as DropDownList;
            if (ddlTableNames.SelectedIndex == 0)
                return;
            
            string tableName = ddlTableNames.SelectedItem.Text.Trim();
            if (tableName.Contains('('))
            {
                tableName = tableName.Remove(tableName.IndexOf('(')).Trim();
            }
            string strSql = " select column_name||'  ('||comments||')' COLUMN_NAME from  user_col_comments t where table_name='" + tableName + "'";
            DataTable dtFieldNames = new DataTable();
            dtFieldNames = OracleHelper.Query(strSql).Tables[0];
            GridViewRow row = (GridViewRow)ddlTableNames.NamingContainer;
            DropDownList ddlFieldName = row.FindControl("ddlFieldName") as DropDownList;
            ddlFieldName.DataSource = dtFieldNames;
            ddlFieldName.DataTextField = "COLUMN_NAME";
            ddlFieldName.DataBind();
            ddlFieldName.Items.Insert(0, new ListItem(" "));
            foreach (ListItem item in ddlFieldName.Items)
            {
                item.Text = item.Text.Replace("()", "");
            }
            ddlFieldName = row.FindControl("ddlPkFieldName") as DropDownList;
            ddlFieldName.DataSource = dtFieldNames;
            ddlFieldName.DataTextField = "COLUMN_NAME";
            ddlFieldName.DataBind();
            ddlFieldName.Items.Insert(0, new ListItem(" "));
            foreach (ListItem item in ddlFieldName.Items)
            {
                item.Text = item.Text.Replace("()", "");
            }

        }
        protected void ddlTbName_SelectedIndexChanged(object sender, EventArgs e)//主键表选项发生变化时
        {
            DropDownList ddlTableNames = sender as DropDownList;
            if (ddlTableNames.SelectedIndex == 0)
                return;
            string tableName = ddlTableNames.SelectedItem.Text.Trim();
            string strSql = " select column_name||'  ('||comments||')' COLUMN_NAME from  user_col_comments t where table_name='" + tableName + "'";
            DataTable dtFieldNames = new DataTable();
            dtFieldNames = OracleHelper.Query(strSql).Tables[0];
            this.ddlFieldName.DataSource = dtFieldNames;
            this.ddlFieldName.DataTextField = "COLUMN_NAME";
            this.ddlFieldName.DataBind();
            this.ddlFieldName.Items.Insert(0, new ListItem(" "));

            foreach (ListItem item in ddlFieldName.Items)
            {
                item.Text = item.Text.Replace("()", "");
            }

        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string faName = txtSjName.Text.Trim();
            string fileContent = hiddenPath.Value;
            string pktablename = this.ddlTbName.Text;
            string pkfield = this.ddlFieldName.Text;
            //DateTime dtNow = DateTime.Now;
            string strSql = "insert into tb_formula(name,value,pktablename,pkfieldname) "
                            + "values('" + faName + "','" + fileContent + "' ,'" + pktablename + "','" + pkfield + "') ";
            OracleHelper.ExecuteSql(strSql);


            ArrayList strsSql = new ArrayList();

            foreach (GridViewRow dr in GridView1.Rows)
            {
                if (dr.RowType == DataControlRowType.DataRow)
                {
                    FormulaBMModel bmModel = new FormulaBMModel();

                    //bmModel.name = dr.Cells[0].Text;//公式名
                    bmModel.bkname = dr.Cells[0].Text;//书签文字
                    bmModel.tablename = (dr.Cells[2].FindControl("ddlTableName") as DropDownList).Text;
                    bmModel.pkfieldname = (dr.Cells[3].FindControl("ddlPkFieldName") as DropDownList).Text;
                    bmModel.fieldname = (dr.Cells[4].FindControl("ddlFieldName") as DropDownList).Text;
                    if( bmModel.tablename.Contains('(') )
                    bmModel.tablename = bmModel.tablename.Remove(bmModel.tablename.IndexOf('(')).Trim();
                    if (bmModel.pkfieldname.Contains('('))
                    bmModel.pkfieldname = bmModel.pkfieldname.Remove(bmModel.pkfieldname.IndexOf('(')).Trim();
                    if (bmModel.fieldname.Contains('('))
                    bmModel.fieldname = bmModel.fieldname.Remove(bmModel.fieldname.IndexOf('(')).Trim();

                    strSql = "insert into tb_formula_var(name,bkname,tablename,pkfieldname,fieldname) values('" + faName + "','" + bmModel.bkname + "','" + bmModel.tablename + "','" + bmModel.pkfieldname + "','" + bmModel.fieldname + "') ";
                    strsSql.Add(strSql);
                }
            }
            OracleHelper.ExecuteSqlTran(strsSql);
            this.lblMessage.Text = "保存成功！";
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            //GridView1.Rows
        }

    }


    public class FormulaBMModel
    {
        public string name;//公式名
        public string bkname;
        public string tablename;
        public string fieldname;
        public string pkfieldname;
    }


    public class MyBookMark1
    {
        string name = "";
        string text = "";
        public MyBookMark1(string markName, string markText)
        {
            name = markName;
            text = markText;
        }
        public string Name
        {
            set { name = value; }
            get { return name; }
        }
        public string Text
        {
            set { text = value; }
            get { return text; }
        }
    }

    public class Tools1
    {

        public static List<MyBookMark1> GetBookMarksFromDoc(string filePath)
        {
            List<MyBookMark1> bookMarks = new List<MyBookMark1>();

            Document tempDocument;
            tempDocument = new Document(filePath);
            foreach (Bookmark bookMark in tempDocument.Range.Bookmarks)
            {
                if (bookMark.Name[0]!= '_')
                    bookMarks.Add(new MyBookMark1(bookMark.Name, bookMark.Text));

            }
            return bookMarks;

        }
        public static void FillDropDownList(DropDownList dropList, List<string> strList)
        {
            foreach (string str in strList)
            {
                dropList.Items.Add(str);
            }
        }
    }
}